package org.nt4b.schemadoc.core.dao.oracle;

import java.util.List;

import org.nt4b.schemadoc.core.dao.SchemaDocDAO;
import org.nt4b.schemadoc.core.dom.Column;
import org.nt4b.schemadoc.core.dom.Table;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;


public class OracleSchemaDocDAO extends JdbcDaoSupport implements SchemaDocDAO {
    
    /**
     * Read all comments from provided connection
     * 
     * @return SchemaComments 
     */
    @Override
    public List<Table> getSchemaComments(final String schemaName) {
        final List<Table> tables = getTableCommentsInternal(schemaName);
        getColumnComments(schemaName, tables);
        
        return tables;
    }
    
    /**
     * Reads comments of all tables in schema 
     * without column comments
     * 
     * @return 
     */
    @Override
    public List<Table> getTableComments(final String schemaName) {
        final List<Table> tables = getTableCommentsInternal(schemaName);
        
        return tables;
    }
    
    /**
     * Reads only comments for single table
     * 
     * @param tableName
     * @return Table
     */
    @Override
    public Table getTableComments(final String schemaName, final String tableName) {
        final String sql = "SELECT * FROM user_tab_comments where table_name = '" + tableName + "'";
        final List<Table> tables = getJdbcTemplate().query(sql, new TableCommentsMapper());
        getColumnComments(schemaName, tables);
        
        if (tables.size() > 0) {
            return tables.get(0);
        }
        return null;
    }
    
    /**
     * Save schema comments.
     */
    public void save(final List<Table> tables) {
        for (final Table table : tables) {
            saveTableComment(table);
            
            if (!CollectionUtils.isEmpty(table.getColumns())) {
                for (final Column column : table.getColumns()) {
                    saveColumnComment(table, column);
                }
            }
        }
    }
    
    /**
     * Internal funktion for getting table comments.
     * Hides all oracle system tables
     * 
     * @param schemaName
     * @return
     */
    private List<Table> getTableCommentsInternal(final String schemaName) {
        final StringBuilder buf = new StringBuilder();
        buf.append("SELECT * FROM \n");
        if (StringUtils.hasText(schemaName)) {
            buf.append("all_tab_comments \n");
            buf.append("WHERE owner = '"+ schemaName+"' \n");
        } else {
            buf.append("user_tab_comments \n");
            buf.append("WHERE 1 = 1 \n");            
        }
        buf.append("   AND table_name NOT LIKE '%$%' \n"); 
        buf.append("ORDER BY table_name");
        
        return getJdbcTemplate().query(buf.toString(), new TableCommentsMapper());
    }
    
    /**
     * Function extracts columns comments.
     * 
     * @param tables
     * @return
     */
    public List<Column> getColumnComments(final String schemaName, final List<Table> tables) {        
        final StringBuilder buf = new StringBuilder();
        buf.append("SELECT u1.* \n");
        if (StringUtils.hasText(schemaName)) {
            buf.append("FROM all_col_comments u1 \n");
            buf.append("JOIN all_tab_columns u2 ON u1.table_name = u2.table_name \n");
            buf.append("                       AND u1.column_name = u2.column_name \n");
            buf.append("                       AND u1.owner = u2.owner \n");
            buf.append("                       AND u1.owner = '" + schemaName + "' \n");
        } else {
            buf.append("FROM user_col_comments u1 \n");
            buf.append("JOIN user_tab_columns u2 ON u1.table_name = u2.table_name \n");
            buf.append("                         AND u1.column_name = u2.column_name \n");            
        }
        if (tables.size() == 1) { // Small performance optimization
            final String tableName = tables.get(0).getName();
            buf.append("WHERE u1.table_name = '" + tableName + "' \n");
        }
        buf.append("ORDER BY u1.table_name, u2.column_id \n");
        
        final String sql = buf.toString();
        final ColumnCommentsMapper mapper = new ColumnCommentsMapper(tables);
        
        return getJdbcTemplate().query(sql, mapper);
    }
    
    private void saveTableComment(final Table table) {
        final String comment;
        if (table.getComment() != null) {
            comment = table.getComment();
        } else {
            comment = "";
        }
        
        final String sql = "COMMENT ON TABLE \"" + table.getName() + "\" IS '" + comment + "'";
        
        try {
            getJdbcTemplate().update(sql);
        } catch (final DataAccessException e) {
            if (e.getMessage().contains("ORA-00904")) {
                logger.warn("Table " + table.getName() + " not found! Comment will not be imported.");
            } else {
                throw e;
            }
        }
    }
    
    private void saveColumnComment(final Table table, final Column column) {
        final String comment;
        if (table.getComment() != null) {
            comment = column.getComment();
        } else {
            comment = "";
        }
        
        final String sql = "COMMENT ON COLUMN \"" + table.getName() + "\".\"" + column.getName() + "\" IS '" + comment + "'";
        
        try {
            getJdbcTemplate().update(sql);
        } catch (final DataAccessException e) {
            if (e.getMessage().contains("ORA-00904")) {
                logger.warn("Column " + table.getName() + "." + column.getName() + " not found! Comment will not be imported.");
            } else {
                throw e;
            }
        }
    }
    
}
